<?php
// Authentication functions
function isLoggedIn() {
    return isset($_SESSION['user_id']);
}

function isAdmin() {
    return isset($_SESSION['user_role']) && $_SESSION['user_role'] === 'admin';
}

function requireLogin() {
    if (!isLoggedIn()) {
        header('Location: /chuan/535g/auth/login.php');
        exit();
    }
}

function requireAdmin() {
    requireLogin();
    if (!isAdmin()) {
        header('Location: /chuan/535g/');
        exit();
    }
}

// User functions
function getUserById($pdo, $user_id) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$user_id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function getUserByEmail($pdo, $email) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
    $stmt->execute([$email]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

// Product functions
function getFeaturedProducts($pdo, $limit = 6) {
    // Check if is_featured column exists
    $stmt = $pdo->query("SHOW COLUMNS FROM products LIKE 'is_featured'");
    $has_featured = $stmt->fetch();
    
    $limit = (int)$limit; // Ensure it's an integer
    
    if ($has_featured) {
        $stmt = $pdo->prepare("SELECT * FROM products WHERE is_featured = 1 AND (is_active = 1 OR is_active IS NULL) ORDER BY id DESC LIMIT " . $limit);
    } else {
        $stmt = $pdo->prepare("SELECT * FROM products WHERE (is_active = 1 OR is_active IS NULL) ORDER BY id DESC LIMIT " . $limit);
    }
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getProductById($pdo, $product_id) {
    // Check if category_id column exists
    $stmt = $pdo->query("SHOW COLUMNS FROM products LIKE 'category_id'");
    $has_category = $stmt->fetch();
    
    if ($has_category) {
        $stmt = $pdo->prepare("SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE p.id = ?");
    } else {
        $stmt = $pdo->prepare("SELECT p.*, 'General' as category_name, NULL as category_id FROM products p WHERE p.id = ?");
    }
    $stmt->execute([$product_id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function getAllProducts($pdo, $limit = 12, $offset = 0) {
    $limit = (int)$limit;
    $offset = (int)$offset;
    
    // Check if category_id column exists
    $stmt = $pdo->query("SHOW COLUMNS FROM products LIKE 'category_id'");
    $has_category = $stmt->fetch();
    
    if ($has_category) {
        $stmt = $pdo->prepare("SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE (p.is_active = 1 OR p.is_active IS NULL) ORDER BY p.id DESC LIMIT " . $limit . " OFFSET " . $offset);
    } else {
        $stmt = $pdo->prepare("SELECT p.*, 'General' as category_name, NULL as category_id FROM products p WHERE (p.is_active = 1 OR p.is_active IS NULL) ORDER BY p.id DESC LIMIT " . $limit . " OFFSET " . $offset);
    }
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getProductsByCategory($pdo, $category_id, $limit = 12) {
    $limit = (int)$limit;
    
    // Check if category_id column exists
    $stmt = $pdo->query("SHOW COLUMNS FROM products LIKE 'category_id'");
    $has_category = $stmt->fetch();
    
    if ($has_category) {
        $stmt = $pdo->prepare("SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE p.category_id = ? AND (p.is_active = 1 OR p.is_active IS NULL) ORDER BY p.id DESC LIMIT " . $limit);
        $stmt->execute([$category_id]);
    } else {
        // If no category_id column, return all products
        $stmt = $pdo->prepare("SELECT p.*, 'General' as category_name FROM products p WHERE (p.is_active = 1 OR p.is_active IS NULL) ORDER BY p.id DESC LIMIT " . $limit);
        $stmt->execute();
    }
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getAllCategories($pdo) {
    $stmt = $pdo->query("SELECT * FROM categories ORDER BY name");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function searchProducts($pdo, $search_term, $limit = 12) {
    $limit = (int)$limit;
    
    // Check if category_id column exists
    $stmt = $pdo->query("SHOW COLUMNS FROM products LIKE 'category_id'");
    $has_category = $stmt->fetch();
    
    $search = "%$search_term%";
    
    if ($has_category) {
        $stmt = $pdo->prepare("SELECT p.*, c.name as category_name FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE (p.name LIKE ? OR p.description LIKE ?) AND (p.is_active = 1 OR p.is_active IS NULL) ORDER BY p.id DESC LIMIT " . $limit);
        $stmt->execute([$search, $search]);
    } else {
        $stmt = $pdo->prepare("SELECT p.*, 'General' as category_name FROM products p WHERE (p.name LIKE ? OR p.description LIKE ?) AND (p.is_active = 1 OR p.is_active IS NULL) ORDER BY p.id DESC LIMIT " . $limit);
        $stmt->execute([$search, $search]);
    }
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Cart functions
function getCartItems($pdo, $user_id) {
    $stmt = $pdo->prepare("
        SELECT ci.*, p.name, p.price, p.image_url, p.stock_quantity
        FROM cart_items ci
        JOIN products p ON ci.product_id = p.id
        WHERE ci.user_id = ?
        ORDER BY ci.created_at DESC
    ");
    $stmt->execute([$user_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCartTotal($pdo, $user_id) {
    $stmt = $pdo->prepare("
        SELECT SUM(ci.quantity * p.price) as total
        FROM cart_items ci
        JOIN products p ON ci.product_id = p.id
        WHERE ci.user_id = ?
    ");
    $stmt->execute([$user_id]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result['total'] ?? 0;
}

function addToCart($pdo, $user_id, $product_id, $quantity = 1) {
    // Check if item already exists in cart
    $stmt = $pdo->prepare("SELECT id, quantity FROM cart_items WHERE user_id = ? AND product_id = ?");
    $stmt->execute([$user_id, $product_id]);
    $existing = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($existing) {
        // Update existing item
        $new_quantity = $existing['quantity'] + $quantity;
        $stmt = $pdo->prepare("UPDATE cart_items SET quantity = ? WHERE id = ?");
        return $stmt->execute([$new_quantity, $existing['id']]);
    } else {
        // Add new item
        $stmt = $pdo->prepare("INSERT INTO cart_items (user_id, product_id, quantity) VALUES (?, ?, ?)");
        return $stmt->execute([$user_id, $product_id, $quantity]);
    }
}

function removeFromCart($pdo, $user_id, $product_id) {
    $stmt = $pdo->prepare("DELETE FROM cart_items WHERE user_id = ? AND product_id = ?");
    return $stmt->execute([$user_id, $product_id]);
}

function updateCartQuantity($pdo, $user_id, $product_id, $quantity) {
    if ($quantity <= 0) {
        return removeFromCart($pdo, $user_id, $product_id);
    }
    
    $stmt = $pdo->prepare("UPDATE cart_items SET quantity = ? WHERE user_id = ? AND product_id = ?");
    return $stmt->execute([$quantity, $user_id, $product_id]);
}

function clearCart($pdo, $user_id) {
    $stmt = $pdo->prepare("DELETE FROM cart_items WHERE user_id = ?");
    return $stmt->execute([$user_id]);
}

// Order functions
function createOrder($pdo, $user_id, $order_data) {
    $pdo->beginTransaction();
    
    try {
        // Generate order number
        $order_number = 'ROI-' . date('Ymd') . '-' . str_pad(rand(1, 9999), 4, '0', STR_PAD_LEFT);
        
        // Check if total_amount column exists
        $stmt = $pdo->query("SHOW COLUMNS FROM orders LIKE 'total_amount'");
        $has_total_amount = $stmt->fetch() !== false;
        
        // Create order with appropriate columns
        if ($has_total_amount) {
            $stmt = $pdo->prepare("
                INSERT INTO orders (user_id, order_number, total_amount, shipping_address, billing_address, payment_method, notes)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ");
            $stmt->execute([
                $user_id,
                $order_number,
                $order_data['total_amount'],
                $order_data['shipping_address'],
                $order_data['billing_address'],
                $order_data['payment_method'],
                $order_data['notes'] ?? ''
            ]);
        } else {
            // Fallback: create order without total_amount column
            $stmt = $pdo->prepare("
                INSERT INTO orders (user_id, order_number, shipping_address, billing_address, payment_method, notes)
                VALUES (?, ?, ?, ?, ?, ?)
            ");
            $stmt->execute([
                $user_id,
                $order_number,
                $order_data['shipping_address'],
                $order_data['billing_address'],
                $order_data['payment_method'],
                $order_data['notes'] ?? ''
            ]);
        }
        
        $order_id = $pdo->lastInsertId();
        
        // Add order items
        $stmt = $pdo->prepare("
            INSERT INTO order_items (order_id, product_id, quantity, price)
            SELECT ?, ci.product_id, ci.quantity, p.price
            FROM cart_items ci
            JOIN products p ON ci.product_id = p.id
            WHERE ci.user_id = ?
        ");
        $stmt->execute([$order_id, $user_id]);
        
        // Clear cart
        clearCart($pdo, $user_id);
        
        $pdo->commit();
        return $order_id;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

function getOrdersByUser($pdo, $user_id) {
    $stmt = $pdo->prepare("
        SELECT o.*, COUNT(oi.id) as item_count
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.user_id = ?
        GROUP BY o.id
        ORDER BY o.created_at DESC
    ");
    $stmt->execute([$user_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getOrderById($pdo, $order_id) {
    $stmt = $pdo->prepare("
        SELECT o.*, u.first_name, u.last_name, u.email
        FROM orders o
        JOIN users u ON o.user_id = u.id
        WHERE o.id = ?
    ");
    $stmt->execute([$order_id]);
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function getOrderItems($pdo, $order_id) {
    $stmt = $pdo->prepare("
        SELECT oi.*, p.name, p.image_url
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        WHERE oi.order_id = ?
    ");
    $stmt->execute([$order_id]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getAllOrders($pdo, $limit = 50) {
    $limit = (int)$limit;
    $stmt = $pdo->prepare("
        SELECT o.*, u.first_name, u.last_name, u.email, COUNT(oi.id) as item_count
        FROM orders o
        JOIN users u ON o.user_id = u.id
        LEFT JOIN order_items oi ON o.id = oi.order_id
        GROUP BY o.id
        ORDER BY o.created_at DESC
        LIMIT " . $limit
    );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Analytics functions
function getSalesAnalytics($pdo, $period = '30') {
    $stmt = $pdo->prepare("
        SELECT 
            DATE(created_at) as date,
            COUNT(*) as order_count,
            SUM(total_amount) as total_sales
        FROM orders 
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
        GROUP BY DATE(created_at)
        ORDER BY date DESC
    ");
    $stmt->execute([$period]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getTopProducts($pdo, $limit = 5) {
    $limit = (int)$limit;
    
    // Check if status column exists in orders table
    $stmt = $pdo->query("SHOW COLUMNS FROM orders");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    $has_status = in_array('status', $columns);
    
    $where_clause = $has_status ? "WHERE o.status != 'cancelled'" : "";
    
    $stmt = $pdo->prepare("
        SELECT 
            p.name,
            p.id,
            SUM(oi.quantity) as total_sold,
            SUM(oi.quantity * oi.price) as total_revenue
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        JOIN orders o ON oi.order_id = o.id
        " . $where_clause . "
        GROUP BY p.id, p.name
        ORDER BY total_sold DESC
        LIMIT " . $limit
    );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCustomerStats($pdo) {
    // Check if required columns exist
    $stmt = $pdo->query("SHOW COLUMNS FROM orders");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $has_total_amount = in_array('total_amount', $columns);
    $has_status = in_array('status', $columns);
    $has_created_at = in_array('created_at', $columns);
    
    if (!$has_total_amount) {
        // If total_amount column doesn't exist, calculate from order_items
        $stmt = $pdo->query("
            SELECT 
                COUNT(*) as total_customers,
                COUNT(CASE WHEN u.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_customers,
                COALESCE(AVG(order_totals.total), 0) as avg_order_value
            FROM users u
            LEFT JOIN (
                SELECT o.user_id, SUM(oi.quantity * oi.price) as total
                FROM orders o
                LEFT JOIN order_items oi ON o.id = oi.order_id
                " . ($has_status ? "WHERE o.status != 'cancelled'" : "") . "
                GROUP BY o.user_id
            ) order_totals ON u.id = order_totals.user_id
            WHERE u.role = 'customer'
        ");
    } else {
        // Use total_amount column if it exists
        $stmt = $pdo->query("
            SELECT 
                COUNT(*) as total_customers,
                COUNT(CASE WHEN u.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_customers,
                COALESCE(AVG(order_totals.total), 0) as avg_order_value
            FROM users u
            LEFT JOIN (
                SELECT user_id, SUM(total_amount) as total
                FROM orders 
                " . ($has_status ? "WHERE status != 'cancelled'" : "") . "
                GROUP BY user_id
            ) order_totals ON u.id = order_totals.user_id
            WHERE u.role = 'customer'
        ");
    }
    
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Ensure all expected keys exist with default values
    return [
        'total_customers' => $result['total_customers'] ?? 0,
        'new_customers' => $result['new_customers'] ?? 0,
        'avg_order_value' => $result['avg_order_value'] ?? 0
    ];
}

function getRevenueStats($pdo) {
    // Check if required columns exist
    $stmt = $pdo->query("SHOW COLUMNS FROM orders");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $has_total_amount = in_array('total_amount', $columns);
    $has_status = in_array('status', $columns);
    $has_created_at = in_array('created_at', $columns);
    
    if (!$has_total_amount) {
        // If total_amount column doesn't exist, calculate from order_items
        $stmt = $pdo->query("
            SELECT 
                COUNT(DISTINCT o.id) as total_orders,
                COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue,
                COALESCE(AVG(order_totals.order_total), 0) as avg_order_value,
                COUNT(DISTINCT CASE WHEN o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN o.id END) as orders_this_month,
                COALESCE(SUM(CASE WHEN o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN order_totals.order_total ELSE 0 END), 0) as revenue_this_month
            FROM orders o
            LEFT JOIN (
                SELECT order_id, SUM(quantity * price) as order_total
                FROM order_items
                GROUP BY order_id
            ) order_totals ON o.id = order_totals.order_id
            LEFT JOIN order_items oi ON o.id = oi.order_id
            " . ($has_status ? "WHERE o.status != 'cancelled'" : "")
        );
    } else {
        // Use total_amount column if it exists
        $stmt = $pdo->query("
            SELECT 
                COUNT(*) as total_orders,
                SUM(total_amount) as total_revenue,
                AVG(total_amount) as avg_order_value,
                COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as orders_this_month,
                SUM(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN total_amount ELSE 0 END) as revenue_this_month
            FROM orders 
            " . ($has_status ? "WHERE status != 'cancelled'" : "")
        );
    }
    
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Ensure all expected keys exist with default values
    return [
        'total_orders' => $result['total_orders'] ?? 0,
        'total_revenue' => $result['total_revenue'] ?? 0,
        'avg_order_value' => $result['avg_order_value'] ?? 0,
        'orders_this_month' => $result['orders_this_month'] ?? 0,
        'revenue_this_month' => $result['revenue_this_month'] ?? 0
    ];
}

// Utility functions
function sanitizeInput($data) {
    return htmlspecialchars(strip_tags(trim($data)));
}

function generateOrderNumber() {
    return 'ROI-' . date('Ymd') . '-' . str_pad(rand(1, 9999), 4, '0', STR_PAD_LEFT);
}

function formatPrice($price) {
    return '$' . number_format($price, 2);
}

function timeAgo($datetime) {
    $time = time() - strtotime($datetime);
    
    if ($time < 60) return 'just now';
    if ($time < 3600) return floor($time/60) . ' minutes ago';
    if ($time < 86400) return floor($time/3600) . ' hours ago';
    if ($time < 2592000) return floor($time/86400) . ' days ago';
    if ($time < 31536000) return floor($time/2592000) . ' months ago';
    return floor($time/31536000) . ' years ago';
}
?>
